{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "修正excel表\n",
    "有点问题，最后是手动调整了下"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Excel文件路径\n",
    "file_path = '../data/Problem_D_Great_Lakes.xlsx'\n",
    "# 读取Excel文件中所有sheets\n",
    "xls = pd.ExcelFile(file_path)\n",
    "\n",
    "# 使用ExcelWriter，以便保存多个sheets\n",
    "with pd.ExcelWriter('../data/Great_Lakes_Water.xlsx', engine='openpyxl') as writer:\n",
    "    for sheet_name in xls.sheet_names:\n",
    "        # 读取当前sheet\n",
    "        df = pd.read_excel(xls, sheet_name=sheet_name)\n",
    "        # 丢弃前6行\n",
    "        modified_df = df.iloc[5:]\n",
    "        # 将修改后的DataFrame写回新的Excel文件\n",
    "        modified_df.to_excel(writer, sheet_name=sheet_name, index=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 修改数据表结构"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Time Lake Superior St. Mary's River Lake Michigan and Lake Huron  \\\n",
      "2000-01  NaN        183.16              ---                       175.92   \n",
      "2000-02  NaN        183.08              ---                       175.87   \n",
      "2000-03  NaN        183.08              ---                        175.9   \n",
      "2000-04  NaN        183.12              ---                       175.92   \n",
      "2000-05  NaN        183.16              ---                        176.0   \n",
      "\n",
      "        St. Clair River Lake St. Clair Detroit River Lake Erie Niagara River  \\\n",
      "2000-01             ---         174.63           ---    173.82          5180   \n",
      "2000-02             ---         174.44           ---    173.75          4980   \n",
      "2000-03             ---         174.66           ---    173.83          5030   \n",
      "2000-04             ---         174.76           ---    173.95          5320   \n",
      "2000-05             ---         174.85           ---    174.07          5570   \n",
      "\n",
      "        Lake Ontario Ottawa River St. Lawrence River  \n",
      "2000-01         74.5         2090                ---  \n",
      "2000-02        74.45         2003                ---  \n",
      "2000-03        74.58         2382                ---  \n",
      "2000-04        74.82         2847                ---  \n",
      "2000-05         75.1         2712                ---  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "\n",
    "# 文件路径\n",
    "file_path = '../data/Great_Lakes_Water.xlsx'\n",
    "xls = pd.ExcelFile(file_path)\n",
    "\n",
    "# 获取所有sheet名称\n",
    "sheet_names = xls.sheet_names\n",
    "\n",
    "# 初始化一个空的DataFrame来汇总所有数据，预先定义列名\n",
    "combined_data = pd.DataFrame(sheet_names)\n",
    "\n",
    "# 英文月份缩写到数字的映射\n",
    "months_map = {datetime.strptime(month, '%b').month: month for month in ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']}\n",
    "\n",
    "for sheet_name in sheet_names:\n",
    "    # 读取当前sheet的数据\n",
    "    data = pd.read_excel(xls, sheet_name=sheet_name)\n",
    "    \n",
    "    # 遍历每一行数据\n",
    "    for i, row in data.iterrows():\n",
    "        # 确保年份是整数形式的字符串\n",
    "        year = str(int(row['Year']))\n",
    "        for month in data.columns[1:]:  # 跳过'Year'列\n",
    "            # 使用英文月份缩写获取对应的数字月份\n",
    "            month_num = list(months_map.keys())[list(months_map.values()).index(month)]\n",
    "            # 构建以年-月（数字）为单位的时间字符串\n",
    "            month_str = f\"{year}-{month_num:02d}\"  # 格式化月份为两位数字\n",
    "            # 检查列名是否已经存在，如果不存在则先创建\n",
    "            if sheet_name not in combined_data.columns:\n",
    "                combined_data[sheet_name] = pd.NA\n",
    "            # 插入数据到DataFrame中\n",
    "            combined_data.at[month_str, sheet_name] = row[month]\n",
    "\n",
    "# 对索引进行排序以确保顺序\n",
    "combined_data = combined_data.sort_index()\n",
    "\n",
    "# 显示汇总数据的前几行进行检查\n",
    "print(combined_data.head())\n",
    "\n",
    "# 保存汇总的数据到Excel文件中\n",
    "combined_data.to_excel('../data/Great_Lakes_Water_Monthly.xlsx',  index=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         Index  Lake Superior St. Mary's River  Lake Michigan and Lake Huron  \\\n",
      "Time                                                                           \n",
      "2000-01      1         183.16              ---                        175.92   \n",
      "2000-02      2         183.08              ---                        175.87   \n",
      "2000-03      3         183.08              ---                        175.90   \n",
      "2000-04      4         183.12              ---                        175.92   \n",
      "2000-05      5         183.16              ---                        176.00   \n",
      "\n",
      "        St. Clair River  Lake St. Clair Detroit River  Lake Erie  \\\n",
      "Time                                                               \n",
      "2000-01             ---          174.63           ---     173.82   \n",
      "2000-02             ---          174.44           ---     173.75   \n",
      "2000-03             ---          174.66           ---     173.83   \n",
      "2000-04             ---          174.76           ---     173.95   \n",
      "2000-05             ---          174.85           ---     174.07   \n",
      "\n",
      "        Niagara River  Lake Ontario Ottawa River St. Lawrence River  \\\n",
      "Time                                                                  \n",
      "2000-01          5180         74.50         2090                ---   \n",
      "2000-02          4980         74.45         2003                ---   \n",
      "2000-03          5030         74.58         2382                ---   \n",
      "2000-04          5320         74.82         2847                ---   \n",
      "2000-05          5570         75.10         2712                ---   \n",
      "\n",
      "         Lake Superior_Change  Lake Michigan and Lake Huron_Change  \\\n",
      "Time                                                                 \n",
      "2000-01                 -0.08                                -0.05   \n",
      "2000-02                  0.00                                 0.03   \n",
      "2000-03                  0.04                                 0.02   \n",
      "2000-04                  0.04                                 0.08   \n",
      "2000-05                  0.08                                 0.10   \n",
      "\n",
      "         Lake St. Clair_Change  Lake Erie_Change  Lake Ontario_Change  \n",
      "Time                                                                   \n",
      "2000-01                  -0.19             -0.07                -0.05  \n",
      "2000-02                   0.22              0.08                 0.13  \n",
      "2000-03                   0.10              0.12                 0.24  \n",
      "2000-04                   0.09              0.12                 0.28  \n",
      "2000-05                   0.11              0.11                 0.16  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 读取处理后的数据\n",
    "file_path = '../data/Great_Lakes_Water_Monthly.xlsx'\n",
    "data = pd.read_excel(file_path, index_col='Time')\n",
    "\n",
    "# 遍历所有列（除去可能的非湖泊列）\n",
    "for column in data.columns:\n",
    "    # 检查列名是否包含“Lake”来确定是否为湖泊字段\n",
    "    if \"Lake\" in column:\n",
    "        # 计算每月变化值\n",
    "        data[column + '_Change'] = data[column].diff().shift(-1)\n",
    "\n",
    "# 保存修改后的数据到新的Excel文件中\n",
    "output_file_path = '../data/Great_Lakes_Water_Monthly_Changes.xlsx'\n",
    "data.to_excel(output_file_path)\n",
    "\n",
    "# 显示修改后的数据的前几行以确认\n",
    "print(data.head())"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "ICM",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
